package com.merck.service.impl;

import com.merck.service.export.GenerateExcelImpl;
import com.merck.utils.DateUtils;
import com.merck.utils.ExportUtil;
import com.merck.utils.JDBCUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.log4j.MDC;
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


//@Service("ImportDataServiceImpl")
public class ImportDataServiceImpl implements ImportData {

    private Logger log = Logger.getLogger(ImportDataServiceImpl.class);

//    @Resource(name = "jdbcTemplate")
//    private JdbcTemplate jdbcTemplate;

    @Override
    public void insertData(List<List<String>> list, Map<Integer, String> mapTitle, String excle, String tableName, Object o, Connection connection) {
//        log.info("开始处理导入表格数据");
        List<Integer> integerList = new ArrayList<Integer>();
        //处理字段名
        String val = "";
        try {
            for (Map.Entry<Integer, String> entry : mapTitle.entrySet()) {
                String value = entry.getValue().trim();
                Integer key = entry.getKey();
                val = val + value + ",";
                integerList.add(key);
            }
            if (val.endsWith(",")) {
                val = val.substring(0, val.lastIndexOf(","));
            }
            val = val.replace(" ", "_");

            //处理内容

            String sql = "";

            String values = "";

            for (int i = 0; i < list.size(); i++) {
                List<String> listVal = list.get(i);

                String tempStr = "(";

                for (Integer integer2 : integerList) {

                    if (integer2 > listVal.size() - 1) {
                        tempStr = tempStr + "'" + "" + "'" + ",";
                    } else {

                        String value = listVal.get(integer2).trim().replace("（", "(").replace("）", ")")
                                .replaceAll("(\\r\\n|\\r|\\n|\\n\\r)+", "");
                        if (value.contains("'")) {
                            value = value.replace("'", "''");
                        }

                        tempStr = tempStr + "'" + value.trim() + "'" + ",";
                    }
                }
                if (tempStr.endsWith(",")) {
                    tempStr = tempStr.substring(0, tempStr.lastIndexOf(","));
                }

                tempStr = tempStr + ")";

                values = values + tempStr + ",";

            }
            if (values.endsWith(",")) {
                values = values.substring(0, values.lastIndexOf(","));
            }

            sql = "insert into " + tableName + "(" + val + ")" + "values" + values;
            if (StringUtils.isNotEmpty(sql)) {
                System.out.println(sql);
            }


//            log.debug("运行sql：" + sql);
            PreparedStatement stat = connection.prepareStatement(sql);
            stat.execute();

            MDC.put("exec_sql", sql.replace("'"," "));

        } catch (SQLException e) {
            log.error(e.getMessage());
        }
//        connection.update(sql);

    }

    @Override
    public void exportDataExcleApp(Map<String, String> map, File exportFile, String type,
                                   Map<String, Map<String, String>> mapTitles, String target) {
        try {
            MDC.put("", new Date());
            if ("excle".equalsIgnoreCase(type)) {
                Workbook wb = null;
                CellStyle cellStyle1 = null;
                CellStyle cellStyle2 = null;
                CellStyle cellStyleAppli = null;
                DataFormat createDataFormatAppli = null;
                DataFormat createDataFormat = null;

                CellStyle cellStyle3 = null;
                DataFormat createDataFormat1 = null;

                CellStyle cellStyle4 = null;
                DataFormat createDataFormat4 = null;

                XSSFCellStyle headStyle = null;
                XSSFCellStyle headStyleAppli = null;
                XSSFCellStyle bodyStyle = null;
                XSSFCellStyle bodyStyleAppli = null;

                XSSFCellStyle bodyStyle2 = null;
                XSSFCellStyle bodyStyleAppli2 = null;

                XSSFCellStyle cellStyle = null;

                Sheet sheet = null; // 工作表对象
                Row nRow = null; // 行对象
                Cell nCell = null; // 列对象
                FileOutputStream fOut = null;
                // 总行号
                int pageRowNo = 0; // 页行号
                boolean flg = true;
//                File exportFile = new File(path);
                File parent = exportFile.getParentFile();
                if (parent != null && !parent.exists()) {
                    parent.mkdirs();
                }
                if (!exportFile.exists()) {
                    exportFile.createNewFile();
                }
                // Map<String, String> maptitle = getMapperMap();
                List<Map<String, Object>> selectData = null;

                for (Map.Entry<String, String> entrys : map.entrySet()) {
                    MDC.put("start_time", new Date());
                    int rowNo = 0;
                    String sql = entrys.getValue();
                    MDC.put("exec_sql", sql.replace("'"," "));
                    String sheetName = entrys.getKey();
                    Map<String, String> maptitle = mapTitles.get(sheetName);
//                    if (sheetName.equalsIgnoreCase("Wei Zhu")) {
//                        sheetName = "T1_East";
//                    } else if (sheetName.equalsIgnoreCase("Adam xu")) {
//                        sheetName = "Digital";
//                    } else if (sheetName.equalsIgnoreCase("Jason Song")) {
//                        sheetName = "T1_North & South";
//                    } else if (sheetName.equalsIgnoreCase("Mingfei Guo")) {
//                        sheetName = "Diagnostic";
//                    } else if (sheetName.equalsIgnoreCase("Seaman Wu")) {
//                        sheetName = "AA I&T";
//                    } else if (sheetName.equalsIgnoreCase("George Ge")) {
//                        sheetName = "Business B";
//                    } else if (sheetName.equalsIgnoreCase("Ida Li")) {
//                        sheetName = "T2_Northeast";
//                    } else if (sheetName.equalsIgnoreCase("Weihan Yang")) {
//                        sheetName = "T2_West";
//                    } else if (sheetName.equalsIgnoreCase("Kevin Xue")) {
//                        sheetName = "T2_Southeast";
//                    } else if (sheetName.equalsIgnoreCase("Clark he")) {
//                        sheetName = "KA";
//                    } else if (sheetName.equalsIgnoreCase("Fen Lu")) {
//                        sheetName = "DG";
//                    } else if (sheetName.equalsIgnoreCase("Michael Ju")) {
//                        sheetName = "BM";
//                    }
//                    if (sheetName.equalsIgnoreCase("Business B")) {
//                        System.out.println("------------");
//                        System.out.println(sql);
//                        System.out.println("------------");
//                    }
                    if (StringUtils.isNoneBlank(sql)) {
                        selectData =  queryForListMap(sql);
//                        selectData = jdbcTemplate.queryForList(sql);

                    }
                    if (wb == null) {
                        wb = new SXSSFWorkbook(100);
                        cellStyle1 = wb.createCellStyle();
                        cellStyle1.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
                        cellStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        ExportUtil exportUtil = new ExportUtil(wb, sheet);
                        headStyle = (XSSFCellStyle) exportUtil.getHeadStyle();
                        headStyleAppli = (XSSFCellStyle) exportUtil.getHeadStyleForAppli();

                        bodyStyle = (XSSFCellStyle) exportUtil.getBodyStyleBord();
                        bodyStyleAppli = (XSSFCellStyle) exportUtil.getBodyStyleAppli();

                        bodyStyle2 = (XSSFCellStyle) exportUtil.getBodyStyleAndLeft();
                        bodyStyleAppli2 = (XSSFCellStyle) exportUtil.getBodyStyleLeftAppli();

                        Font font = wb.createFont();
                        font.setFontName("微软雅黑");
                        font.setFontHeight((short) 200);

                        Font font2 = wb.createFont();
                        font2.setFontName("微软雅黑");
                        font2.setFontHeight((short) 220);

                        cellStyle2 = wb.createCellStyle();
                        createDataFormat = wb.createDataFormat();
                        cellStyle2.setDataFormat(createDataFormat.getFormat("0%"));
                        cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
                        cellStyle2.setAlignment(HorizontalAlignment.LEFT);
                        cellStyle2.setFont(font);
                        cellStyle2.setBorderLeft(BorderStyle.THIN);
                        cellStyle2.setBorderBottom(BorderStyle.THIN);
                        cellStyle2.setBorderRight(BorderStyle.THIN);
                        cellStyle2.setBorderTop(BorderStyle.THIN);

                        cellStyleAppli = wb.createCellStyle();
                        createDataFormatAppli = wb.createDataFormat();
                        cellStyleAppli.setDataFormat(createDataFormatAppli.getFormat("0%"));
//                        cellStyleAppli.setVerticalAlignment(VerticalAlignment.CENTER);
//                        cellStyleAppli.setAlignment(HorizontalAlignment.CENTER);
//                        cellStyleAppli.setFont(font2);
//                        cellStyleAppli.setBorderLeft(BorderStyle.THIN);
//                        cellStyleAppli.setBorderBottom(BorderStyle.THIN);
//                        cellStyleAppli.setBorderRight(BorderStyle.THIN);
//                        cellStyleAppli.setBorderTop(BorderStyle.THIN);

                        cellStyle3 = wb.createCellStyle();
                        createDataFormat1 = wb.createDataFormat();
                        cellStyle3.setDataFormat(createDataFormat1.getFormat("#,##0.00"));
//                        cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
//                        cellStyle3.setAlignment(HorizontalAlignment.LEFT);
//                        cellStyle3.setFont(font);
//                        cellStyle3.setBorderLeft(BorderStyle.THIN);
//                        cellStyle3.setBorderBottom(BorderStyle.THIN);
//                        cellStyle3.setBorderRight(BorderStyle.THIN);
//                        cellStyle3.setBorderTop(BorderStyle.THIN);

                        cellStyle4 = wb.createCellStyle();
                        createDataFormat4 = wb.createDataFormat();
                        cellStyle4.setDataFormat(createDataFormat4.getFormat("#,##0"));
                        cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);
                        cellStyle4.setAlignment(HorizontalAlignment.CENTER);
                        cellStyle4.setFont(font2);
                        cellStyle4.setBorderLeft(BorderStyle.THIN);
                        cellStyle4.setBorderBottom(BorderStyle.THIN);
                        cellStyle4.setBorderRight(BorderStyle.THIN);
                        cellStyle4.setBorderTop(BorderStyle.THIN);
//						cellStyle = (XSSFCellStyle) wb.createCellStyle();
//						cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0%"));

                    }
                    //if (!CollectionUtils.isEmpty(selectData) && maptitle != null) {
                    Integer totalFlg = null;
                    int f = 0;
                    int sizeTotal = selectData.size() + 1;
                    if (rowNo % 1000000 == 0) {
                        sheet = wb.createSheet(sheetName);
                        //int f = 0;
                        for (Map.Entry<String, String> entry : maptitle.entrySet()) {
                            String key = entry.getValue();
                            sheet.setColumnWidth(f, 25 * 256);
//                            if (key.toUpperCase().contains("TEAM") || key.toUpperCase().contains("NAME") || key.toUpperCase().contains("DESCRIPTION")) {
//
//                                sheet.setColumnWidth(f, 25 * 256);
//                            } else if (key.toUpperCase().contains("HEAD")) {
//                                sheet.setColumnWidth(f, 18 * 256);
//                            } else if (key.replaceAll(" +", "").toUpperCase().contains("ORDERRELEASEMATRIX")) {
//                                sheet.setColumnWidth(f, 28 * 256);
//                            } else {
//                                sheet.setColumnWidth(f, 13 * 256);
//
//                            }
//                            if ("Order Value".equals(key.trim())) {
//                                totalFlg = f;
//                            }
//                            f++;
                        }

                        // sheet = wb.getSheetAt(rowNo / 1000000);
                        //如果是导出模板
                        if ("temp".equals(target)) {
                            pageRowNo = 0;
                            nRow = sheet.createRow(pageRowNo); // 新建行对象
                            int n = 0;
                            //将标题map的key放入表头
                            for (Map.Entry<String, String> entry : maptitle.entrySet()) {
                                nCell = nRow.createCell(n++);
                                nCell.setCellValue(entry.getKey());
                                nCell.setCellStyle(headStyle);
                            }
                            nRow = sheet.createRow(++pageRowNo); // 新建行对象
                            n = 0;
                            for (Map.Entry<String, String> entry : maptitle.entrySet()) {
                                nCell = nRow.createCell(n++);
                                nCell.setCellValue(entry.getValue());
                                nCell.setCellStyle(headStyle);
                            }
                        }else {
                            //否则就是导出只有中文的
                            pageRowNo = 0;
                            nRow = sheet.createRow(pageRowNo); // 新建行对象
                            int n = 0;
                            for (Map.Entry<String, String> entry : maptitle.entrySet()) {
                                nCell = nRow.createCell(n++);
                                nCell.setCellValue(entry.getValue());
                                nCell.setCellStyle(headStyle);
                            }

                        }


                    }
                    if (!CollectionUtils.isEmpty(selectData) && maptitle != null) {
                        for (int k = 0; k < selectData.size(); k++) {
                            //sales team对应
//                            if (selectData.get(k).containsKey("HEAD")) {
//                                String saleHeadName = null;
//                                if (selectData.get(k).get("HEAD") != null) {
//                                    saleHeadName = selectData.get(k).get("HEAD").toString();
//                                    if (saleHeadName.equalsIgnoreCase("Wei Zhu")) {
//                                        selectData.get(k).put("HEAD", "T1_East");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("Adam xu")) {
//                                        selectData.get(k).put("HEAD", "Digital");
//                                    }
//                                    //if (saleHeadName.equalsIgnoreCase("Channel")) {
//                                    //    selectData.get(k).put("HEAD", "Channel");
//                                    //}
//                                    if (saleHeadName.equalsIgnoreCase("Jason Song")) {
//                                        selectData.get(k).put("HEAD", "T1_North & South");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("Mingfei Guo")) {
//                                        selectData.get(k).put("HEAD", "Diagnostic");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("Seaman Wu")) {
//                                        selectData.get(k).put("HEAD", "AA I&T");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("George Ge")) {
//                                        selectData.get(k).put("HEAD", "Business B");
//                                    }
//                                    //if (saleHeadName.equalsIgnoreCase("Steve Vermant")) {
//                                    //    selectData.get(k).put("HEAD", "Business A");
//                                    //}
//                                    if (saleHeadName.equalsIgnoreCase("Ida Li")) {
//                                        selectData.get(k).put("HEAD", "T2_Northeast");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("Weihan Yang")) {
//                                        selectData.get(k).put("HEAD", "T2_West");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("Kevin Xue")) {
//                                        selectData.get(k).put("HEAD", "T2_Southeast");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("Clark he")) {
//                                        selectData.get(k).put("HEAD", "KA");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("Fen Lu")) {
//                                        selectData.get(k).put("HEAD", "DG");
//                                    }
//                                    if (saleHeadName.equalsIgnoreCase("Michael Ju")) {
//                                        selectData.get(k).put("HEAD", "BM");
//                                    }
//
//                                }
//                            }
                            rowNo++;
                            nRow = sheet.createRow(++pageRowNo); // 新建行对象
                            int n = 0;
                            for (Map.Entry<String, String> entry : maptitle.entrySet()) {

                                String key = entry.getKey();
                                Object object = selectData.get(k).get(key);
                                nCell = nRow.createCell(n++);
                                if (object != null) {
                                    String s = object.toString();

                                    //当字段为分数，做format
//                                    if ( key.contains("1_p")|| key.contains("2_p")|| key.contains("3_p")){
//                                        nCell.setCellValue(Float.valueOf(s));
//                                        nCell.setCellStyle(cellStyleAppli);
//                                    }else
                                        if (key.contains("score_q_p")|| key.contains("score_q_ps")|| key.contains("total")){
                                        BigDecimal bg = new BigDecimal(s);
                                        nCell.setCellValue(bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
                                        nCell.setCellStyle(cellStyleAppli);
                                    }else  if ( key.contains("1_p")|| key.contains("2_p")|| key.contains("3_p") || key.contains("q_p")){
                                        nCell.setCellValue(Float.valueOf(s));
                                        nCell.setCellStyle(cellStyleAppli);
                                    }
                                    else if (key.contains("coaching_quality_m1")||key.contains("coaching_quality_m2")||key.contains("coaching_quality_m3")
                                    || key.contains("department_meeting_quality_m1")|| key.contains("department_meeting_quality_m2")|| key.contains("department_meeting_quality_m3")
                                            || key.contains("core_hospital_rate_q") ||key.contains("lesson_fee_in_time_q") || key.contains("lesson_fee_return_rate_q")
                                                || key.contains("lesson_fee_in_time_and_return_rate_q")|| key.contains("rep_meeting_execution_rate_q")
                                                || key.contains("phase_breakthrough_speaker_coverage_q")|| key.contains("event_execution_q")|| key.contains("speaker_coverage_q")
                                    ){
                                        nCell.setCellValue(s);
                                    } else if (key.contains("daily_actual_calls_m1") || key.contains("daily_actual_calls_m2")
                                            || key.contains("daily_actual_calls_m3") || key.contains("daily_prefessional_calls_m1")
                                            || key.contains("daily_prefessional_calls_m2")|| key.contains("daily_prefessional_calls_m3")) {
//                                        nCell.setCellValue(Float.valueOf(s));
                                        BigDecimal bg = new BigDecimal(s);
                                        nCell.setCellValue(bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue());
                                    } else if (key.contains("_q") || key.contains("_m1") || key.contains("_m2") || key.contains("_m3") || key.contains("on_job_months")) {
                                        if (s.contains("%")) {
                                            nCell.setCellValue(s);
                                        } else {
                                            nCell.setCellValue(Float.valueOf(s));
                                        }
                                    } else {
                                        nCell.setCellValue(s);
                                    }
//                                    String s1 = s.replaceAll(" +", "").replace(",", "");
//                                    if (s1.matches("^-?\\d+$") || s1.matches("^(-?\\d+)(\\.\\d+)?$")) {
//                                        String s2 = s.replace(",", "");
//                                        if ("-0".equals(s2.replaceAll(" +", ""))) {
//                                            s2 = "0";
//                                        }

//                                        try {
//                                            nCell.setCellValue(Double.valueOf(s2));
//                                            String keys = entry.getKey();
//                                            if (map.containsKey("Wei Zhu") || map.containsKey("Jason Song")
//                                                    || map.containsKey("Weihan Yang") || map.containsKey("Mingfei Guo")
//                                                    || map.containsKey("George Ge") || map.containsKey("Adam xu")
//                                                    || map.containsKey("Ida Li")
//                                                    || map.containsKey("Kevin Xue") || map.containsKey("Clark he")
//                                                    || map.containsKey("Fen Lu") || map.containsKey("Michael Ju")
//                                                    || map.containsKey("Seaman Wu") || map.containsKey("Business A")) {
//
//                                                if ("CREDIT_VALUE".equals(keys) || "DAYS_30_M".equals(keys)
//                                                        || "OPEN_ORDER".equals(keys) || "CREDIT_LIMIT".equals(keys)
//                                                        || "EXPOSURE".equals(keys) || "OVERRUNS".equals(keys)
//                                                        || "RECEIVABLES".equals(keys)) {
//                                                    nCell.setCellStyle(cellStyle4);
//                                                } else {
//                                                    nCell.setCellStyle(bodyStyleAppli);
//                                                }
//
//                                            } else {
//
//                                                if (map.containsKey("block order" + target)
//                                                        || map.containsKey("Release order" + target)
//                                                        || map.containsKey("无放单意见") || map.containsKey("无欠款卡单")) {
//
//                                                    if ("CC".equals(keys) || "DOCUMENT_NUMBER".equals(keys)
//                                                            || "PARTNER".equals(keys)
//                                                            || "EXTERNAL_REFER".equals(keys)) {
//                                                        nCell.setCellStyle(bodyStyle);
//                                                    } else {
//                                                        nCell.setCellStyle(cellStyle3);
//                                                    }
//
//                                                }
//
//                                            }

//                                        } catch (Exception e) {
//                                            nCell.setCellValue(s);
                                    System.out.println(s);
//                                            System.out.println(s2);
//                                        }
//                                    }
//                                    else if (s1.matches("^-?\\d+%$")) {

//                                        if (map.containsKey("Wei Zhu") || map.containsKey("Jason Song")
//                                                || map.containsKey("Weihan Yang") || map.containsKey("Mingfei Guo")
//                                                || map.containsKey("George Ge") || map.containsKey("Adam xu")
//                                                || map.containsKey("Ida Li")
//                                                || map.containsKey("Kevin Xue") || map.containsKey("Clark he")
//                                                || map.containsKey("Fen Lu") || map.containsKey("Michael Ju")
//                                                || map.containsKey("Seaman Wu") || map.containsKey("Business A")) {
//                                            nCell.setCellStyle(cellStyleAppli);
//                                        } else {
//
//                                            nCell.setCellStyle(cellStyle2);
//                                        }

//                                        nCell.setCellValue(Double.valueOf(s.replace("%", "")) / 100);
                                    ;
//                                    }
//                                    else {
//                                        nCell.setCellValue(s);
                                    String keys = entry.getKey();

//                                        if (map.containsKey("Wei Zhu") || map.containsKey("Jason Song")
//                                                || map.containsKey("Weihan Yang") || map.containsKey("Mingfei Guo")
//                                                || map.containsKey("George Ge") || map.containsKey("Adam xu")
//                                                || map.containsKey("Ida Li")
//                                                || map.containsKey("Kevin Xue") || map.containsKey("Clark he")
//                                                || map.containsKey("Fen Lu") || map.containsKey("Michael Ju")
//                                                || map.containsKey("Seaman Wu") || map.containsKey("Business A")) {
//
//                                            if (keys.toUpperCase().contains("NAME")
//                                                    || keys.toUpperCase().contains("DESCRIPTION")) {
//
//                                                nCell.setCellStyle(bodyStyleAppli2);
//                                            } else {
//
//                                                nCell.setCellStyle(bodyStyleAppli);
//                                            }
//                                        } else {
//
//                                            if (keys.toUpperCase().contains("NAME")
//                                                    || keys.toUpperCase().contains("DESCRIPTION")) {
//
//                                                nCell.setCellStyle(bodyStyle2);
//                                            } else {
//
//                                                nCell.setCellStyle(bodyStyle);
//                                            }
//                                        }


                                }
                            }

                        }
//                        if (totalFlg != null) {
//                            ExportUtil exportUtil = new ExportUtil(wb, sheet);
//                            XSSFCellStyle headStyle2 = (XSSFCellStyle) exportUtil.getHeadStyleNoborderAndNopoint();
//
//                            String colString = CellReference.convertNumToColString(totalFlg);
//                            String sumstring = "SUM(" + colString + "2:" + colString + sizeTotal + ")";// 求和公式
//                            nRow = sheet.createRow(++pageRowNo);
//
//                            for (int i = 0; i < f; i++) {
//                                if (i == (totalFlg - 2)) {
//                                    nCell = nRow.createCell(totalFlg - 2 > 0 ? totalFlg - 2 : 0);
//                                    nCell.setCellValue("Total");
//                                    nCell.setCellStyle(headStyle2);
//                                } else if (i == totalFlg) {
//                                    nCell = nRow.createCell(totalFlg);
//                                    nCell.setCellFormula(sumstring);
//                                    nCell.setCellStyle(headStyle2);
//                                } else {
//                                    nCell = nRow.createCell(i);
//                                    nCell.setCellValue("");
//                                    nCell.setCellStyle(headStyle2);
//                                }
//
//                            }
//
//
//                        }

                    }
                    MDC.put("end_time", new Date());
                    log.info("当前sheet页写入成功");
                }
                try {
                    if (wb != null) {
                        fOut = new FileOutputStream(exportFile);
                        wb.write(fOut);
                        fOut.flush();

                    }
                } catch (IOException e) {
                    log.error(e.getMessage());
                    throw e;
                } finally {
                    fOut.close();
                }
            }
            //新需求开始3
//            if (approvalFile.exists()) {
//                approvalFile.delete();
//                log.info("Sap4.3 已删除邮件审批触发文件：approval.txt");
//            }
            //新需求结束3
        } catch (Exception e) {
            log.error("导出excle异常：" + e.getMessage());
            throw new RuntimeException("导出excle异常：" + e.getMessage());
        }

    }

    public List<Map<String, Object>> queryForListMap(String sql) {
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Connection conn = null;
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            conn = JDBCUtils.getConnection();
//            PreparedStatement alps = conn.prepareStatement("ALTER database postgres SET search_path TO lego_kpi_calculation");
//            alps.execute();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            final ResultSetMetaData metaData = rs.getMetaData();
            final int columnCount = metaData.getColumnCount();
            //遍历加入
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(metaData.getColumnLabel(i), rs.getObject(i));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        }finally {
            try {
                JDBCUtils.close();
            } catch (SQLException e) {
                log.error("queryForListMap 异常："+e.getMessage());
            }
        }
        return list;

    }
}
